using System;
using System.Collections.Generic;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Text;
using Publicuse.Entity;
using System.Linq;
using Publicuse.Entity.AttributeUtil;
using Publicuse.Util;

namespace Publicuse.DBCore.Oracle
{
    public class MyOracleInstance
    {
        private string connstr;

        private static object locker = new object();

        private static MyOracleInstance OracleInstance = null;

        static MyOracleInstance()
        {
            OracleInstance = new MyOracleInstance();
        }

        public static MyOracleInstance GetInstance
        {
            get { return OracleInstance; }
        }

        /// <summary>
        /// 数据库实例字段初始化
        /// </summary>
        /// <param name="constr"></param>
        public void Init(string constr)
        {
            if (string.IsNullOrEmpty(constr))
                return;

            this.connstr = constr;
        }

        /// <summary>
        /// 存储过程参数封装
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="param"></param>
        /// <param name="sqlParamsType"></param>
        /// <returns></returns>
        private bool parameterCom(IDbCommand sqlcmd, object param, SqlParamsType sqlParamsType)
        {
            if (param == null)
            {
                return true;
            }
            if (!(param is object[]))
            {
                return false;
            }
            if (sqlParamsType == SqlParamsType.Default)
            {
                object[] array = param as object[];
                if (array.Length % 2 != 0)
                {
                    return false;
                }
                for (int i = 0; i < array.Length; i += 2)
                {
                    OracleParameter OracleParameter = new OracleParameter();
                    OracleParameter.ParameterName = array[i].ToString();
                    OracleParameter.Value = array[i + 1];
                    if (array[i + 2] != null)
                    {
                        OracleParameter.Direction = (array[i + 2]).ToString() == "1" ? ParameterDirection.Input : ParameterDirection.Output;
                    }
                    sqlcmd.Parameters.Add(OracleParameter);
                }
            }
            else if (sqlParamsType == SqlParamsType.Params)
            {
                object[] array = param as object[];
                foreach (var pi in array)
                    sqlcmd.Parameters.Add(pi);
            }

            return true;
        }


        /// <summary>
        /// 泛型操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="M"></typeparam>
        /// <param name="sql"></param>
        /// <param name="isproc"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public M EexcuteCommon<T, M>(string sql, bool isproc, Func<IDbCommand, M> func) where T : new()
        {
            using (IDbConnection con = new OracleConnection(this.connstr))
            {
                con.Open();
                IDbCommand dbCommand = con.CreateCommand();
                dbCommand.CommandText = sql;
                dbCommand.CommandType = (isproc ? CommandType.StoredProcedure : CommandType.Text);
                return func(dbCommand);
            }
        }

        /// <summary>
        /// 通用CUD操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="cUDType"></param>
        /// <param name="exception"></param>
        /// <param name="sql"></param>
        /// <returns>返回操作结果</returns>
        public int CUDEntity<T>(T entity, CUDType cUDType, out string exception, string sql = null)
        {
            exception = null;
            var sqlParameter = new OracleParameter[] { };
            CURDModel cURDModel = null;
            if (string.IsNullOrEmpty(sql))
            {
                Type type = typeof(T);
                T t = (T)Activator.CreateInstance(type);
                var files = type.GetProperties();

                var tableName = TableAtributeUtil.GetTable<T>();
                switch (cUDType)
                {
                    case CUDType.Add:
                        {
                            var columnnames = String.Join(",", files.Take(files.Length - 2).Select(o => o.Name));
                            var columnvalues = String.Join(",", files.Take(files.Length - 2).Select(p => string.Format("@{0}", p.Name)));
                            sql = "insert into {0} ({1}) values({2});select {3};";
                            sql = string.Format(sql, tableName, columnnames, columnvalues, " LAST_INSERT_ID()");
                            sqlParameter = files.Take(files.Length - 2).Select(p => new OracleParameter(string.Format("@{0}", p.Name), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                        }
                        break;
                    case CUDType.Update:
                        {
                            var updatenames = String.Join(",", files.Take(files.Length - 2).Select(p => string.Format("{0}=@{1}", p.Name, p.Name)));
                            var key = TableAtributeUtil.GetPrimaryKey<T>();
                            var Key = string.Format("{0}=@{1}", key, key);
                            sql = string.Format("update {0} set {1} where {2};", tableName, updatenames, Key);
                            sqlParameter = files.Take(files.Length - 2).Select(p => new OracleParameter(string.Format("@{0}", p.Name), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                        }
                        break;
                    case CUDType.Delete:
                        {
                            var key = TableAtributeUtil.GetPrimaryKey<T>();
                            var Key = string.Format("{0}=@{1}", key, key);
                            sql = string.Format("delete from {0} where {1};", tableName, Key);
                            sqlParameter = files.Skip(files.Length - 4).Take(1).Select(p => new OracleParameter(string.Format("@{0}", p.Name), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                        }
                        break;
                }
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Params, cUDType = cUDType };
            }
            else
            {
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Default, cUDType = cUDType };
            }
            return ExecuteNonQuery(sql, sqlParameter, cURDModel, false, out exception);
        }

        /// <summary>
        /// 批量新增修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="cUDType"></param>
        /// <param name="exception"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<int> CUDEntity<T>(List<T> entitys, CUDType cUDType, out string exception, string sql = null)
        {
            exception = null;
            var sqlParameter = new OracleParameter[] { };
            CURDModel cURDModel = null;
            List<OracleParameter[]> listsqlParameter = new List<OracleParameter[]>();
            List<string> listsql = new List<string>();
            if (string.IsNullOrEmpty(sql))
            {
                Type type = typeof(T);
                T t = (T)Activator.CreateInstance(type);
                var files = type.GetProperties();

                var tableName = TableAtributeUtil.GetTable<T>();
                foreach (var entity in entitys)
                {
                    switch (cUDType)
                    {
                        case CUDType.Add:
                            {
                                var columnnames = String.Join(",", files.Take(files.Length - 2).Select(o => o.Name));
                                var columnvalues = String.Join(",", files.Take(files.Length - 2).Select(p => string.Format("@{0}", p.Name)));
                                sql = "insert into {0} ({1}) values({2});select {3};";
                                sql = string.Format(sql, tableName, columnnames, columnvalues, " LAST_INSERT_ID()");
                                sqlParameter = files.Take(files.Length - 2).Take(files.Length - 2).Select(p => new OracleParameter(string.Format("@{0}", p.Name), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                            }
                            break;
                        case CUDType.Update:
                            {
                                var updatenames = String.Join(",", files.Take(files.Length - 2).Select(p => string.Format("{0}=@{1}", p.Name, p.Name)));
                                var key = TableAtributeUtil.GetPrimaryKey<T>();
                                var Key = string.Format("{0}=@{1}", key, key);
                                sql = string.Format("update {0} set {1} where {2};", tableName, updatenames, Key);
                                sqlParameter = files.Take(files.Length - 2).Select(p => new OracleParameter(string.Format("@{0}", p.Name), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                            }
                            break;
                        case CUDType.Delete:
                            {
                                var key = TableAtributeUtil.GetPrimaryKey<T>();
                                var Key = string.Format("{0}=@{1}", key, key);
                                sql = string.Format("delete from {0} where {1};", tableName, Key);
                                sqlParameter = files.Skip(files.Length - 2).Take(2).Select(p => new OracleParameter(string.Format("@{0}", p.Name), p.GetValue(entity) ?? DBNull.Value)).ToArray();
                            }
                            break;
                    }
                    listsqlParameter.Add(sqlParameter);
                    listsql.Add(sql);
                }
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Params, cUDType = cUDType };
            }
            else
            {
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Default, cUDType = cUDType };
            }
            return ExecuteNonQuery(listsql, listsqlParameter, cURDModel, false, out exception);
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private T InitConnction<T>(out string msg, Func<OracleCommand, T> action)
        {
            msg = string.Empty;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    OracleCommand cmd = new OracleCommand();
                    con.Open();
                    cmd.Connection = con;
                    return action.Invoke(cmd);
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return default(T);
            }
        }

        /// <summary>
        /// 批量操作
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="cURDModel"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public List<int> ExecuteNonQuery(List<string> sqlstr, List<OracleParameter[]> param, CURDModel cURDModel, bool isproc, out string exception)
        {
            exception = null;
            List<int> result = new List<int>();
            OracleTransaction tran = null;

            result = InitConnction(out exception, (cmd) =>
            {
                using (tran = cmd.Connection.BeginTransaction())
                {
                    cmd.Transaction = tran;
                    var index = 0;
                    foreach (var sql in sqlstr)
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(param[index]); ;
                        cmd.CommandType = (isproc ? CommandType.StoredProcedure : CommandType.Text);
                        var res = cURDModel.cUDType == CUDType.Add ? Convert.ToInt32(cmd.ExecuteScalar()) : cmd.ExecuteNonQuery();
                        if (res < 0)
                        {
                            if (tran != null)
                                tran.Rollback();

                            result.Add(-1);
                        }
                        else
                        {
                            result.Add(res);
                        }
                        index++;
                    }
                    tran.Commit();
                    return result;
                }
            });
            return result;
        }

        /// <summary>
        /// 泛型执行SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public T ExecuteSql<T>(string sql, out string msg, Func<IDbCommand, bool> func)
        {
            msg = null;
            try
            {
                using (var con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand com = new OracleCommand(sql, con))
                    {
                        con.Open();
                        com.Connection = con;
                        using (OracleTransaction tran = con.BeginTransaction())
                        {
                            com.Transaction = tran;
                            var res = func.Invoke(com);
                            if (res)
                                tran.Commit();
                            else
                                tran.Rollback();

                            return default(T);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return default(T);
            }
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public bool ExecuteSql(string sql, out string msg, Func<IDbCommand, bool> func)
        {
            msg = null;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand com = new OracleCommand(sql, con))
                    {
                        con.Open();
                        com.Connection = con;
                        using (OracleTransaction tran = con.BeginTransaction())
                        {
                            com.Transaction = tran;
                            var res = func.Invoke(com);
                            if (res)
                                tran.Commit();
                            else
                                tran.Rollback();

                            return res;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return false;
            }
        }

        /// <summary>
        /// 根据主键获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Id"></param>
        /// <param name="exception"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool GetEntityById<T>(int Id, out string exception, out T t)
        {
            Type type = typeof(T);
            t = (T)Activator.CreateInstance(type);
            string sql = "select {0} from {1} where {2}";
            var files = type.GetProperties();
            string columenames = String.Join(",", files.Take(files.Length - 2).Select(o => o.Name));
            var key = TableAtributeUtil.GetPrimaryKey<T>();
            var Key = string.Format("{0}=@{1}", key, key);
            var tableName = TableAtributeUtil.GetTable<T>();
            sql = string.Format(sql, columenames, tableName, Key);
            var sqlParam = new OracleParameter(string.Format("@{0}", key), Id);
            exception = null;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    con.Open();
                    IDbCommand dbCommand = con.CreateCommand();
                    dbCommand.CommandText = sql;
                    dbCommand.Parameters.Add(sqlParam);

                    var dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    if (dataReader.Read())
                    {
                        foreach (var pi in type.GetProperties())
                        {
                            var protype = pi.GetCustomAttributes(typeof(NoQueryAttribute), true);
                            if (protype.Count() > 0)
                                continue;

                            if (dataReader[pi.Name] != DBNull.Value)
                            {
                                pi.SetValue(t, dataReader[pi.Name]);
                            }
                            else
                            {
                                switch (pi.PropertyType.FullName)
                                {
                                    case "System.Int32":
                                        pi.SetValue(t, -9);
                                        break;
                                    case "System.Int64":
                                        pi.SetValue(t, -9);
                                        break;
                                    case "System.Float":
                                        pi.SetValue(t, -9);
                                        break;
                                    case "System.String":
                                        pi.SetValue(t, string.Empty);
                                        break;
                                    case "System.DateTime":
                                        pi.SetValue(t, new DateTime(1911, 1, 1));
                                        break;
                                }
                            }
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                exception = ex.Message;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return false;
            }
        }

        /// <summary>
        /// 查询列表信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sqlWhere"></param>
        /// <param name="sortFile"></param>
        /// <param name="srotType"></param>
        /// <param name="count"></param>
        /// <param name="exception"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool GetList<T>(string storedprocedure, string tablename, string queryFile, int pageIndex, int pageSize, string sqlWhere,
            string sortFile, string srotType, out long count, out string exception, out List<T> list)
        {
            list = new List<T>();
            count = 0;
            exception = string.Empty;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand cmd = new OracleCommand())
                    {
                        con.Open();
                        cmd.Connection = con;
                        cmd.CommandText = storedprocedure;
                        cmd.CommandType = CommandType.StoredProcedure;

                        OracleParameter table_name = new OracleParameter("p_table_name", OracleDbType.NVarchar2);
                        table_name.Direction = ParameterDirection.Input;
                        table_name.Value = tablename;

                        OracleParameter files = new OracleParameter("p_files", OracleDbType.NVarchar2);
                        files.Direction = ParameterDirection.Input;
                        files.Value = queryFile;

                        OracleParameter curr_page = new OracleParameter("p_curr_page", OracleDbType.Int32);
                        curr_page.Direction = ParameterDirection.Input;
                        curr_page.Value = pageIndex;

                        OracleParameter page_size = new OracleParameter("p_page_size", OracleDbType.Int32);
                        page_size.Direction = ParameterDirection.Input;
                        page_size.Value = pageSize;

                        OracleParameter order_string = new OracleParameter("p_order_string", OracleDbType.NVarchar2);
                        order_string.Direction = ParameterDirection.Input;
                        order_string.Value = "order by " + sortFile;

                        OracleParameter ordertype_string = new OracleParameter("p_ordertype_string", OracleDbType.NVarchar2);
                        ordertype_string.Direction = ParameterDirection.Input;
                        ordertype_string.Value = string.IsNullOrEmpty(srotType) ? "desc" : srotType;

                        OracleParameter where_string = new OracleParameter("p_where_string", OracleDbType.NVarchar2);
                        where_string.Direction = ParameterDirection.Input;
                        where_string.Value = sqlWhere;

                        OracleParameter total_rows = new OracleParameter("p_total_rows", OracleDbType.Int32);
                        total_rows.Direction = ParameterDirection.Output;

                        cmd.Parameters.AddRange(new OracleParameter[] { table_name, files, curr_page, page_size, order_string, ordertype_string, where_string, total_rows });

                        var item = cmd.ExecuteReader();
                        while (item.Read())
                        {
                            Type type = typeof(T);
                            T entity = (T)Activator.CreateInstance(type);
                            foreach (var pi in type.GetProperties())
                            {
                                var protype = pi.GetCustomAttributes(typeof(NoQueryAttribute), true);
                                if (protype.Count() > 0)
                                    continue;

                                if (item[pi.Name] != DBNull.Value)
                                {
                                    switch (pi.PropertyType.FullName)
                                    {
                                        case "System.Double":
                                            if (string.IsNullOrEmpty(item[pi.Name].ToString()))
                                                pi.SetValue(entity, Convert.ToDouble(0));
                                            else
                                                pi.SetValue(entity, Convert.ToDouble(item[pi.Name]));
                                            break;
                                        case "System.Int64":
                                            if (string.IsNullOrEmpty(item[pi.Name].ToString()))
                                                pi.SetValue(entity, 0);
                                            else
                                                pi.SetValue(entity, Convert.ToInt64(item[pi.Name]));
                                            break;
                                        case "System.Int32":
                                            if (string.IsNullOrEmpty(item[pi.Name].ToString()))
                                                pi.SetValue(entity, 0);
                                            else
                                                pi.SetValue(entity, Convert.ToInt32(item[pi.Name]));
                                            break;
                                        case "System.Float":
                                            pi.SetValue(entity, Convert.ToDouble(item[pi.Name]));
                                            break;
                                        case "System.String":
                                            pi.SetValue(entity, Convert.ToString(item[pi.Name]));
                                            break;
                                        case "System.DateTime":
                                            pi.SetValue(entity, Convert.ToDateTime(item[pi.Name]));
                                            break;
                                        case "System.TimeSpan":
                                            pi.SetValue(entity, TimeSpan.Parse(item[pi.Name].ToString()));
                                            break;
                                    }
                                }
                                else
                                {
                                    switch (pi.PropertyType.FullName)
                                    {
                                        case "System.Int32":
                                            pi.SetValue(entity, -9);
                                            break;
                                        case "System.Int64":
                                            pi.SetValue(entity, -9);
                                            break;
                                        case "System.Float":
                                            pi.SetValue(entity, -9);
                                            break;
                                        case "System.String":
                                            pi.SetValue(entity, string.Empty);
                                            break;
                                        case "System.DateTime":
                                            pi.SetValue(entity, new DateTime(1911, 1, 1));
                                            break;
                                        case "System.TimeSpan":
                                            pi.SetValue(entity, TimeSpan.Parse(item[pi.Name].ToString()));
                                            break;
                                    }
                                }
                            }
                            list.Add(entity);
                        }
                        item.Dispose();
                        count = Convert.ToInt64(cmd.Parameters["@p_total_rows"].Value);
                        //OracleDataAdapter sda = new OracleDataAdapter(cmd);
                        //DataTable dt = new DataTable();
                        //sda.Fill(dt);
                        //if (dt.Rows.Count > 0)
                        //{
                        //  count = Convert.ToInt64(total_rows.Value);
                        //  foreach (DataRow item in dt.Rows)
                        //  {
                        //    Type type = typeof(T);
                        //    T entity = (T)Activator.CreateInstance(type);
                        //    foreach (var pi in type.GetProperties())
                        //    {
                        //      var protype = pi.GetCustomAttributes(typeof(NoQueryAttribute), true);
                        //      if (protype.Count() > 0)
                        //        continue;

                        //      if (item[pi.Name] != DBNull.Value)
                        //      {
                        //        switch (pi.PropertyType.FullName)
                        //        {
                        //          case "System.Double":
                        //            if (string.IsNullOrEmpty(item[pi.Name].ToString()))
                        //              pi.SetValue(entity, Convert.ToDouble(0));
                        //            else
                        //              pi.SetValue(entity, Convert.ToDouble(item[pi.Name]));
                        //            break;
                        //          case "System.Int64":
                        //            if (string.IsNullOrEmpty(item[pi.Name].ToString()))
                        //              pi.SetValue(entity, 0);
                        //            else
                        //              pi.SetValue(entity, Convert.ToInt64(item[pi.Name]));
                        //            break;
                        //          case "System.Int32":
                        //            if (string.IsNullOrEmpty(item[pi.Name].ToString()))
                        //              pi.SetValue(entity, 0);
                        //            else
                        //              pi.SetValue(entity, Convert.ToInt32(item[pi.Name]));
                        //            break;
                        //          case "System.Float":
                        //            pi.SetValue(entity, Convert.ToDouble(item[pi.Name]));
                        //            break;
                        //          case "System.String":
                        //            pi.SetValue(entity, Convert.ToString(item[pi.Name]));
                        //            break;
                        //          case "System.DateTime":
                        //            pi.SetValue(entity, Convert.ToDateTime(item[pi.Name]));
                        //            break;
                        //          case "System.TimeSpan":
                        //            pi.SetValue(entity, TimeSpan.Parse(item[pi.Name].ToString()));
                        //            break;
                        //        }
                        //      }
                        //      else
                        //      {
                        //        switch (pi.PropertyType.FullName)
                        //        {
                        //          case "System.Int32":
                        //            pi.SetValue(entity, -9);
                        //            break;
                        //          case "System.Int64":
                        //            pi.SetValue(entity, -9);
                        //            break;
                        //          case "System.Float":
                        //            pi.SetValue(entity, -9);
                        //            break;
                        //          case "System.String":
                        //            pi.SetValue(entity, string.Empty);
                        //            break;
                        //          case "System.DateTime":
                        //            pi.SetValue(entity, new DateTime(1911, 1, 1));
                        //            break;
                        //          case "System.TimeSpan":
                        //            pi.SetValue(entity, TimeSpan.Parse(item[pi.Name].ToString()));
                        //            break;
                        //        }
                        //      }
                        //    }
                        //    list.Add(entity);
                        //  }
                        //}
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return false;
            }
        }

        /// <summary>
        /// 查询信息列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool GetList<T>(string sqlstr, object param, bool isproc, out string exception, out List<T> list)
        {
            list = new List<T>();
            exception = null;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand com = new OracleCommand())
                    {
                        con.Open();
                        com.CommandText = sqlstr;
                        com.Connection = con;
                        com.CommandType = (isproc ? CommandType.StoredProcedure : CommandType.Text);
                        if (!this.parameterCom(com, param, SqlParamsType.Default))
                        {
                            exception = "参数错误";
                            bool result = false;
                            return result;
                        }

                        var data = com.ExecuteReader();
                        while (data.Read())
                        {
                            Type type = typeof(T);
                            T entity = (T)Activator.CreateInstance(type);
                            foreach (var pi in type.GetProperties())
                            {
                                var protype = pi.GetCustomAttributes(typeof(NoQueryAttribute), true);
                                if (protype.Count() > 0)
                                    continue;

                                if (data[pi.Name] != DBNull.Value)
                                {
                                    pi.SetValue(entity, data[pi.Name]);
                                }
                                else
                                {
                                    switch (pi.PropertyType.FullName)
                                    {
                                        case "System.Int32":
                                            pi.SetValue(entity, -9);
                                            break;
                                        case "System.Int64":
                                            pi.SetValue(entity, -9);
                                            break;
                                        case "System.Float":
                                            pi.SetValue(entity, -9);
                                            break;
                                        case "System.String":
                                            pi.SetValue(entity, string.Empty);
                                            break;
                                        case "System.DateTime":
                                            pi.SetValue(entity, new DateTime(1911, 1, 1));
                                            break;
                                    }
                                }
                            }
                            list.Add(entity);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
                bool result = false;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return result;
            }
            return true;
        }

        /// <summary>
        /// DataTable
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public bool GetDataTable(string sqlstr, object param, bool isproc, out string exception, out DataTable dataTable)
        {
            dataTable = null;
            exception = null;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand com = new OracleCommand())
                    {
                        com.CommandText = sqlstr;
                        com.Connection = con;
                        com.CommandType = (isproc ? CommandType.StoredProcedure : CommandType.Text);
                        if (!this.parameterCom(com, param, SqlParamsType.Default))
                        {
                            exception = "参数错误";
                            bool result = false;
                            return result;
                        }
                        using (OracleDataAdapter OracleDataAdapter = new OracleDataAdapter(com))
                        {
                            dataTable = new DataTable();
                            OracleDataAdapter.Fill(dataTable);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                exception = ex.Message;
                bool result = false;
                return result;
            }
            return true;
        }

        /// <summary>
        /// DataSet
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public bool GetDataSet(string sqlstr, object param, bool isproc, out string exception, out DataSet dataSet)
        {
            dataSet = null;
            exception = null;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand com = new OracleCommand())
                    {
                        com.CommandText = sqlstr;
                        com.Connection = con;
                        com.CommandType = (isproc ? CommandType.StoredProcedure : CommandType.Text);
                        if (!this.parameterCom(com, param, SqlParamsType.Default))
                        {
                            exception = "参数错误";
                            bool result = false;
                            return result;
                        }
                        using (OracleDataAdapter OracleDataAdapter = new OracleDataAdapter(com))
                        {
                            dataSet = new DataSet();
                            OracleDataAdapter.Fill(dataSet);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
                bool result = false;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return result;
            }
            return true;
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="cURDModel"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlstr, object param, CURDModel cURDModel, bool isproc, out string exception)
        {
            exception = null;
            int result = -1;
            OracleTransaction tran = null;

            using (OracleConnection con = new OracleConnection(this.connstr))
            {
                try
                {
                    con.Open();
                    IDbCommand dbCommand = con.CreateCommand();
                    using (tran = con.BeginTransaction())
                    {
                        dbCommand.Transaction = tran;
                        dbCommand.CommandText = sqlstr;
                        dbCommand.CommandType = (isproc ? CommandType.StoredProcedure : CommandType.Text);
                        if (!this.parameterCom(dbCommand, param, cURDModel.sqlParamsType))
                        {
                            exception = "参数错误";
                            result = -1;
                        }
                        else
                        {
                            result = cURDModel.cUDType == CUDType.Add ? Convert.ToInt32(dbCommand.ExecuteScalar()) : dbCommand.ExecuteNonQuery();
                            if (result < 0)
                            {
                                if (tran != null)
                                    tran.Rollback();
                            }
                            else
                            {
                                tran.Commit();
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    exception = ex.Message;
                    result = -1;
                    if (ex.Message.Contains("timed out"))
                    {
                        //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public bool ExecuteScalar(string sqlstr, object param, bool isproc, out string exception, out object result)
        {
            exception = null;
            result = null;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand com = new OracleCommand())
                    {
                        com.CommandText = sqlstr;
                        com.Connection = con;
                        com.CommandType = (isproc ? CommandType.StoredProcedure : CommandType.Text);
                        if (!this.parameterCom(com, param, SqlParamsType.Default))
                        {
                            exception = "参数错误";
                            bool result2 = false;
                            return result2;
                        }
                        con.Open();
                        result = com.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
                bool result2 = false;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
                return result2;
            }
            return true;
        }

        /// <summary>
        /// ExecuteTranSaction
        /// </summary>
        /// <param name="sqllist"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public bool ExecuteTranSaction(List<string> sqllist, out string exception)
        {
            exception = null;
            if (sqllist == null || sqllist.Count == 0)
            {
                exception = "参数错误";
                return false;
            }
            bool result;
            try
            {
                using (OracleConnection con = new OracleConnection(this.connstr))
                {
                    using (OracleCommand com = new OracleCommand())
                    {
                        con.Open();
                        com.Connection = con;
                        using (OracleTransaction tran = con.BeginTransaction())
                        {
                            com.Transaction = tran;
                            com.CommandType = CommandType.Text;
                            bool flag = true;
                            foreach (var sql in sqllist)
                            {
                                com.CommandText = sql;
                                if (0 >= com.ExecuteNonQuery())
                                {
                                    tran.Rollback();
                                    exception = "rollback: " + com;
                                    flag = false;
                                    break;
                                }
                            }
                            if (flag)
                            {
                                tran.Commit();
                            }
                            tran.Dispose();
                            result = flag;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
                result = false;
                if (ex.Message.Contains("timed out"))
                {
                    //LogUtil.GetInstance.WriteLogOld("连接Oracle失败", this.connstr, true);
                }
            }
            return result;
        }
    }
}
